﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using MyMVC.Model;
using System.Data.SqlClient;
using MyMVC.DAL;
using System.Web;

namespace MyMVC.BLL
{
    public class EmployeeService
    {

        /// <summary>
        /// 调用查询操作，查询所有信息，状态为1，表示未假删除的
        /// </summary>
        /// <returns></returns>
        public List<Employee> GetList() {
            string sql = "select * from Employee where state=1";
            SqlDataReader sr = DBHelper.GetReader(sql);
            List<Employee> em = new List<Employee>();
            while (sr.Read()) {
                Employee el = load(sr);
                em.Add(el);
            }
            sr.Close();
            return em;
        }

        /// <summary>
        /// 调用查询操作，查询所有已删除信息，状态为0的
        /// </summary>
        /// <returns></returns>
        public List<Employee> GetList2()
        {
            string sql = "select * from Employee where state=0";
            SqlDataReader sr = DBHelper.GetReader(sql);
            List<Employee> em = new List<Employee>();
            while (sr.Read())
            {
                Employee el = load(sr);
                em.Add(el);
            }
            sr.Close();
            return em;
        }

        /// <summary>
        /// 根据id查询数据信息
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public List<Employee> getlistid(int id) {
            string sql = "select * from Employee where EmpId="+id+"";
            SqlDataReader sr = DBHelper.GetReader(sql);
            List<Employee> em = new List<Employee>();
            while (sr.Read())
            {
                Employee el = load(sr);
                em.Add(el);
            }
            sr.Close();
            return em;
        }

        /// <summary>
        /// 执行查询操作
        /// </summary>
        /// <param name="dr"></param>
        /// <returns></returns>
        private Employee load(SqlDataReader dr) {
            Employee el = new Employee();
            el.EmpId = Convert.ToInt32(dr[0]);
            el.EmpName = dr[1].ToString();
            el.Age = Convert.ToInt32(dr[2]);
            el.Sex = dr[3].ToString();
            el.AddTime = Convert.ToDateTime(dr[4]);
            el.Wage = Convert.ToDouble(dr[5]);
            el.image = dr[8].ToString();

            el.department = new DepartmentService().GetDepartmentID((int)dr["DepId"]);
            return el;
        }

        /// <summary>
        /// 假删除操作
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public int del(int id) {
            string sql = "update Employee Set State=0 where EmpId=@id";
            return DBHelper.NonQuery(sql, sps: new SqlParameter("@id",id));
        }

        /// <summary>
        /// 还原
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public int huanyuan(int id)
        {
            string sql = "update Employee Set State=1 where EmpId=@id";
            return DBHelper.NonQuery(sql, sps: new SqlParameter("@id", id));
        }

        /// <summary>
        /// 添加数据
        /// </summary>
        /// <param name="emp"></param>
        /// <returns></returns>
        public int add(Employee emp,string u) {
            string sql = "insert into Employee values(@EmpName,@Age,@Sex,@AddTime,@Wage,@DepId,1,@image)";
            SqlParameter[] sp = new SqlParameter[]
            {
                new SqlParameter("@EmpName",emp.EmpName),
                new SqlParameter("@Age",emp.Age),
                new SqlParameter("@Sex",emp.Sex),
                new SqlParameter("@AddTime",emp.AddTime),
                new SqlParameter("@Wage",emp.Wage),
                new SqlParameter("@DepId",emp.DepId),
                new SqlParameter("@image","/Image/"+u)
            };
            return DBHelper.NonQuery(sql,sps:sp);
        }

        /// <summary>
        /// 修改数据
        /// </summary>
        /// <param name="emp"></param>
        /// <param name="u"></param>
        /// <param name="id"></param>
        /// <returns></returns>
        public int update(Employee emp,string u,int id)
        {
            string sql = "update Employee set EmpName=@EmpName,Age=@Age,Sex=@Sex,AddTime=@AddTime,Wage=@Wage,DepId=@DepId,1,image=@image where EmpId=@id";
            SqlParameter[] sp = new SqlParameter[]
           {
                new SqlParameter("@EmpName",emp.EmpName),
                new SqlParameter("@Age",emp.Age),
                new SqlParameter("@Sex",emp.Sex),
                new SqlParameter("@AddTime",emp.AddTime),
                new SqlParameter("@Wage",emp.Wage),
                new SqlParameter("@DepId",emp.DepId),
                new SqlParameter("@image","/Image/"+u),
                new SqlParameter("@id",emp.EmpId)
           };
            return DBHelper.NonQuery(sql, sps: sp);
        }

    }
}
